﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;
using System.Configuration;


namespace PMS.DAL
{
    public class ScheduleDAL
    {
        #region Common
        private static ScheduleENT CreateScheduleFromReader(IDataReader reader)
        {
            ScheduleENT item = new ScheduleENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.ScheduleID.ToString()))) item.ScheduleID = Utility.IntParse(reader[ScheduleColumns.ScheduleID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.ScheduleName.ToString()))) item.ScheduleName = Utility.StringParse(reader[ScheduleColumns.ScheduleName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.ProjectID.ToString()))) item.ProjectID = Utility.IntParse(reader[ScheduleColumns.ProjectID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.Description.ToString()))) item.Description = Utility.StringParse(reader[ScheduleColumns.Description.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.OwnerID.ToString()))) item.OwnerID = Utility.IntParse(reader[ScheduleColumns.OwnerID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.SemesterID.ToString()))) item.SemesterID = Utility.IntParse(reader[ScheduleColumns.SemesterID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.Status.ToString()))) item.Status = Utility.StringParse(reader[ScheduleColumns.Status.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[ScheduleColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[ScheduleColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[ScheduleColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[ScheduleColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[ScheduleColumns.Isdeleted.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, ScheduleColumns.ProjectName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.ProjectName.ToString()))) item.ProjectName = Utility.StringParse(reader[ScheduleColumns.ProjectName.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleColumns.Owner.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.Owner.ToString()))) item.Owner = Utility.StringParse(reader[ScheduleColumns.Owner.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleColumns.StartDate.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.StartDate.ToString()))) item.StartDate = Utility.DateTimeParse(reader[ScheduleColumns.StartDate.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleColumns.EndDate.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.EndDate.ToString()))) item.EndDate = Utility.DateTimeParse(reader[ScheduleColumns.EndDate.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleColumns.Groups.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.Groups.ToString()))) item.Groups = Utility.IntParse(reader[ScheduleColumns.Groups.ToString()]);

                }
                if (Utility.FieldExist(dt, ScheduleColumns.SemesterName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ScheduleColumns.SemesterName.ToString()))) item.SemesterName = Utility.StringParse(reader[ScheduleColumns.SemesterName.ToString()]);

                }
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static ScheduleENT GetScheduleByID(int scheduleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleGetByID.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);

                ScheduleENT schedule = new ScheduleENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        schedule = CreateScheduleFromReader(reader);
                    }
                    reader.Close();
                }

                return schedule;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleByID");
            }
        }

        public static ScheduleCollection GetScheduleList(string scheduleName, int projectID, int semesterID, int userID, string status, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleGetList.ToString());
                ScheduleCollection listSchedule = new ScheduleCollection();

                database.AddInParameter(dbCommand, "@ScheduleName", DbType.String, scheduleName);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSchedule.Add(CreateScheduleFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listSchedule;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleList");
            }
        }

        public static ScheduleCollection GetScheduleListForCMB(int userID, int projectID,int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                ScheduleCollection listSchedule = new ScheduleCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSchedule.Add(CreateScheduleFromReader(reader));
                    }
                    reader.Close();
                }

                return listSchedule;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleListForCMB");
            }
        }

        public static ScheduleCollection GetScheduleListByStatus(string scheduleID, string status)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleGetListByStatus.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.String, scheduleID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);

                ScheduleCollection listSchedule = new ScheduleCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSchedule.Add(CreateScheduleFromReader(reader));
                    }
                    reader.Close();
                }

                return listSchedule;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleListForCMB");
            }
        }

        public static ScheduleCollection GetScheduleListByOwnerAndStatus(int userID, string status, string scheduleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleGetListByOwnerAndStatus.ToString());

                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.String, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                ScheduleCollection listSchedule = new ScheduleCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSchedule.Add(CreateScheduleFromReader(reader));
                    }
                    reader.Close();
                }

                return listSchedule;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleListForCMB");
            }
        }

        #endregion

        #region Insert, Update, Delete

        public static int InsertSchedule(ScheduleENT schedule, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleInsert.ToString());
                int scheduleID = 0;
                database.AddOutParameter(dbCommand, "@ScheduleID", DbType.Int32, schedule.ScheduleID);
                database.AddInParameter(dbCommand, "@ScheduleName", DbType.String, schedule.ScheduleName);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, schedule.ProjectID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, schedule.OwnerID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, schedule.SemesterID);
                database.AddInParameter(dbCommand, "@Description", DbType.String, schedule.Description);
                database.AddInParameter(dbCommand, "@Status", DbType.String, schedule.Status);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, schedule.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, schedule.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, schedule.Isdeleted);

                database.ExecuteNonQuery(dbCommand, trans);
                scheduleID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleID"));
                return scheduleID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertPriceList");
            }
        }

        public static int InsertSchedule(ScheduleENT schedule)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                using (DbConnection conn = database.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();

                    try
                    {
                        int scheduleID = InsertSchedule(schedule, trans);
                        if (scheduleID > 0)
                        {
                            foreach (ScheduleItemENT item in schedule.ListScheduleItem)
                            {
                                item.ScheduleID = scheduleID;
                                item.CreatedBy = Utility.UserID;
                                item.UpdateBy = Utility.UserID;
                                int scheduleItemID = ScheduleItemDAL.InsertScheduleItem(item, trans);
                                if (scheduleItemID <= 0)
                                {
                                    scheduleID = -2;
                                    trans.Rollback();
                                    return scheduleID;
                                }
                            }
                            trans.Commit();
                        }
                        else
                        {
                            trans.Rollback();
                        }


                        return scheduleID;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        LogDAL.LogEx("BLM_ERR_InsertSchedule", ex);
                        throw new DataAccessException("BLM_ERR_InsertSchedule");
                    }
                    catch (DataAccessException)
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (ConfigurationException ex)
            {
                LogDAL.LogEx("BLM_ERR_Configuration", ex);
                throw new DataAccessException("BLM_ERR_Configuration");
            }
            catch (SqlException)
            {
                throw;
            }
            catch (DataAccessException)
            {
                throw;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx("BLM_ERR_InsertSchedule", ex);
                throw new DataAccessException("BLM_ERR_InsertSchedule");
            }
        }

        public static int UpdateSchedule(ScheduleENT schedule, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleUpdate.ToString());

                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, schedule.ScheduleID);
                database.AddInParameter(dbCommand, "@ScheduleName", DbType.String, schedule.ScheduleName);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, schedule.ProjectID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, schedule.OwnerID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, schedule.SemesterID);
                database.AddInParameter(dbCommand, "@Description", DbType.String, schedule.Description);
                database.AddInParameter(dbCommand, "@Status", DbType.String, schedule.Status);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, schedule.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, schedule.Isdeleted);

                if (database.ExecuteNonQuery(dbCommand, trans) > 0)
                {
                    int scheduleID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleID"));
                    return scheduleID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertPriceList");
            }
        }

        public static int UpdateSchedule(ScheduleENT schedule)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                using (DbConnection conn = database.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();

                    try
                    {
                        int scheduleID = UpdateSchedule(schedule, trans);
                        if (scheduleID > 0)
                        {
                            string deletedID = string.Empty;

                            foreach (ScheduleItemENT item in schedule.ListScheduleItem)
                            {
                                item.UpdateBy = Utility.UserID;
                                int id = item.ScheduleItemID;
                                if (id <= 0)
                                {
                                    item.ScheduleID = schedule.ScheduleID;
                                    int scheduleItemID = ScheduleItemDAL.InsertScheduleItem(item, trans);
                                    if (scheduleItemID <= 0)
                                    {
                                        scheduleID = -2;
                                        trans.Rollback();
                                        return scheduleID;
                                    }
                                    else
                                    {
                                        deletedID += scheduleItemID + ",";
                                    }
                                }
                                else
                                {
                                    int scheduleItemID = ScheduleItemDAL.UpdateScheduleItem(item, trans);
                                    if (scheduleItemID <= 0)
                                    {
                                        scheduleID = -3;
                                        trans.Rollback();
                                        return scheduleID;
                                    }
                                    else
                                    {
                                        deletedID += scheduleItemID + ",";
                                    }
                                }
                            }

                            if (deletedID.EndsWith(","))
                            {
                                deletedID = deletedID.Remove(deletedID.Length - 1);
                            }

                            ScheduleItemDAL.DeleteScheduleItemNotInID(deletedID, scheduleID, Utility.UserID, trans);
                            trans.Commit();
                            return scheduleID;


                        }
                        else
                        {
                            trans.Rollback();
                        }


                        return scheduleID;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        LogDAL.LogEx("BLM_ERR_UpdateSchedule", ex);
                        throw new DataAccessException("BLM_ERR_UpdateSchedule");
                    }
                    catch (DataAccessException)
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (ConfigurationException ex)
            {
                LogDAL.LogEx("BLM_ERR_Configuration", ex);
                throw new DataAccessException("BLM_ERR_Configuration");
            }
            catch (SqlException)
            {
                throw;
            }
            catch (DataAccessException)
            {
                throw;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx("BLM_ERR_UpdateSchedule", ex);
                throw new DataAccessException("BLM_ERR_UpdateSchedule");
            }
        }

        public static int DeleteSchedule(string scheduleID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleDelete.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.String, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertPriceList");
            }
        }

        public static int ConfirmSchedule(string scheduleID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleConfirm.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.String, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertPriceList");
            }
        }

        public static int JoinSchedule(int scheduleID, int groupID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleJoin.ToString());
                int project_groupID = 0;
                database.AddOutParameter(dbCommand, "@ScheduleGroupID", DbType.Int32, 0);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.String, scheduleID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                database.ExecuteNonQuery(dbCommand);
                project_groupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleGroupID"));
                return project_groupID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_JoinSchedule");
            }
        }

        public static int UnJoinSchedule(int scheduleID, int groupID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleUnJoin.ToString());
               
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.String, scheduleID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_JoinSchedule");
            }
        }
        #endregion

        #region Validate
        public static bool CheckScheduleStatus(int[] scheduleID, string status)
        {
            try
            {
                if (scheduleID.Length == GetScheduleListByOwnerAndStatus(0, status, Utility.StringParse(scheduleID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckScheduleStatus");
            }
        }

        public static bool CheckIsOwnAndDraft(int[] projectID, int userID)
        {
            try
            {
                if (projectID.Length == GetScheduleListByOwnerAndStatus(userID, ScheduleStatus.Draft.ToString(), Utility.StringParse(projectID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckIsOwnAndDraft");
            }
        }

        public static ScheduleENT CheckIsSchedule(int projectID, int groupID, int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCheckIsSchedule.ToString());
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                ScheduleENT schedule = new ScheduleENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        schedule = CreateScheduleFromReader(reader);
                    }
                    reader.Close();
                }

                return schedule;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleByID");
            }
        }
        #endregion
    }
}
